package com.liferay.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.liferay.dao.ConnectDao;
import com.liferay.dao.SubjectOpenDao;
import com.liferay.model.ClassSubject;
import com.liferay.model.Lesson;
import com.liferay.model.ListSubjectOpen;
import com.liferay.model.Room;
import com.liferay.model.SubjectOpen;
import com.liferay.model.Teacher;

public class SubjectOpenDaoImpl implements SubjectOpenDao {
	private static final String GET_CLASS_BY_FACULTY = "SELECT idClass,nameOfClass FROM nlu_class,nlu_faculty WHERE nlu_class.fkFaculty = nlu_faculty.idFaculty AND nlu_class.fkFaculty = ?;";
	private static final String GET_LIST_ROOM = "SELECT * FROM nlu_room;";
	private static final String GET_LIST_LESSON = "SELECT * FROM nlu_lesson;";
	private static final String GET_TEACHER_BY_FACULTY = "SELECT u.userId, u.lastName, u.middleName, u.firstName FROM user_ u, users_facultys uf, users_roles ur, role_ r  "
			+ "WHERE u.userId=uf.userId and uf.idFaculty=? and u.userId=ur.userId and ur.roleId=r.roleId and r.name=?;";
	private static final String INSERT_SUBJECT_OPEN_BY_FACULTY = "INSERT INTO nlu_subjectopen(fkSubject,fkTeacher,fkRoom,fkClass,fkYearOfSemester,fkLesson,date_start,date_end) VALUES (?,?,?,?,?,?,?,?);";
	private static final String GET_LIST_SUBJECT_OPEN_BY_YEAROFSEMESTER = "SELECT DISTINCT nlu_subjectopen.idSubjectOpen,nlu_subject.nameSubject,user_.firstName,user_.middleName,user_.lastName,nlu_room.nameRoom,nlu_lesson.rankOfLesson,nlu_class.nameOfClass,nlu_yearofsemester.semester,nlu_yearofsemester.yearOfSemester,date_start,date_end,weekdays FROM nlu_subjectopen "
			+ "INNER JOIN nlu_subject ON nlu_subject.idSubject = nlu_subjectopen.fkSubject "
			+ "INNER JOIN nlu_class ON nlu_class.idClass = nlu_subjectopen.fkClass "
			+ "INNER JOIN nlu_lesson ON nlu_lesson.idLesson = nlu_subjectopen.fkLesson "
			+ "INNER JOIN nlu_room ON nlu_room.idRoom = nlu_subjectopen.fkRoom "
			+ "INNER JOIN user_ ON user_.userId = nlu_subjectopen.fkTeacher "
			+ "INNER JOIN nlu_faculty ON nlu_faculty.idFaculty = nlu_subject.fkFaculty "
			+ "INNER JOIN nlu_yearofsemester ON nlu_yearofsemester.id = nlu_subjectopen.fkYearOfSemester "
			+ "WHERE nlu_faculty.idFaculty=? AND nlu_subjectopen.fkYearOfSemester =?;";
	private static final String DELETE_SUBJECT_OPEN_BY_ID = "DELETE FROM nlu_subjectopen WHERE nlu_subjectopen.idSubjectOpen = ?";
	private static final String GET_SUBJECT_OPEN_BY_ID ="";
	private List<ClassSubject> listClassSubject;
	private List<Room> listRoom;
	private List<Lesson> listLesson;
	private List<Teacher> listTeacher;
	private List<ListSubjectOpen> listSubjectOpen;
	private List<SubjectOpen> listSbOpen;
	private PreparedStatement pre;
	private ResultSet rs;
	private Connection conn;
	private ClassSubject classSubject;
	private Room room;
	private Lesson lesson;
	private Teacher teacher;

	@Override
	public List<ClassSubject> getClassByFaculty(long idFaculty) {
		conn = ConnectDao.getConnection();
		listClassSubject = new ArrayList<ClassSubject>();
		try {
			pre = conn.prepareStatement(GET_CLASS_BY_FACULTY);
			pre.setLong(1, idFaculty);
			rs = pre.executeQuery();
			while (rs.next()) {
				classSubject = new ClassSubject();
				classSubject.setIdClass(rs.getLong("idClass"));
				classSubject.setNameOfClass(rs.getString("nameOfClass"));
				classSubject.setFkFaculty(idFaculty);
				listClassSubject.add(classSubject);
			}
			rs.close();
			pre.close();
			ConnectDao.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listClassSubject;
	}

	@Override
	public List<Room> getListRoom() {
		conn = ConnectDao.getConnection();
		listRoom = new ArrayList<Room>();
		try {
			pre = conn.prepareStatement(GET_LIST_ROOM);
			rs = pre.executeQuery();
			while (rs.next()) {
				room = new Room();
				room.setIdRoom(rs.getInt("idRoom"));
				room.setNameRoom(rs.getString("nameRoom"));
				listRoom.add(room);
			}
			rs.close();
			pre.close();
			ConnectDao.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listRoom;
	}

	@Override
	public List<Lesson> getListLesson() {
		conn = ConnectDao.getConnection();
		listLesson = new ArrayList<Lesson>();
		try {
			pre = conn.prepareStatement(GET_LIST_LESSON);
			rs = pre.executeQuery();
			while (rs.next()) {
				lesson = new Lesson();
				lesson.setIdLesson(rs.getInt("idLesson"));
				lesson.setRankOfLesson(rs.getString("rankOfLesson"));
				listLesson.add(lesson);
			}
			rs.close();
			pre.close();
			ConnectDao.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listLesson;
	}

	@Override
	public List<Teacher> getTeacherByFaculty(long idFaculty, String roleName) {
		conn = ConnectDao.getConnection();
		listTeacher = new ArrayList<Teacher>();
		try {
			pre = conn.prepareStatement(GET_TEACHER_BY_FACULTY);
			pre.setLong(1, idFaculty);
			pre.setString(2, roleName);
			rs = pre.executeQuery();
			while (rs.next()) {
				teacher = new Teacher();
				teacher.setIdTeacher(rs.getLong("userId"));
				teacher.setNameTeacher(rs.getString("lastName") + " "
						+ rs.getString("middleName") + " "
						+ rs.getString("firstName"));
				teacher.setFkFaculty(idFaculty);
				listTeacher.add(teacher);
			}
			rs.close();
			pre.close();
			ConnectDao.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listTeacher;
	}

	@Override
	public boolean insertSubjectOpenByFaculty(SubjectOpen subjectOpen) {
		conn = ConnectDao.getConnection();
		try {
			pre = conn.prepareStatement(INSERT_SUBJECT_OPEN_BY_FACULTY);
			pre.setLong(1, subjectOpen.getFkSubject());
			pre.setLong(2, subjectOpen.getFkTeacher());
			pre.setInt(3, subjectOpen.getFkRoom());
			pre.setLong(4, subjectOpen.getFkClass());
			pre.setLong(5, subjectOpen.getFkYearOfSemester());
			pre.setLong(6, subjectOpen.getFkLesson());
			pre.setString(7, subjectOpen.getDate_start());
			pre.setString(8, subjectOpen.getDate_end());
			pre.executeUpdate();
			pre.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public List<ListSubjectOpen> getListSubjectOpenByYearOfSemester(
			long idFaculty, long fkYearOfSemester) {
		conn = ConnectDao.getConnection();
		listSubjectOpen = new ArrayList<ListSubjectOpen>();
		try {
			pre = conn
					.prepareStatement(GET_LIST_SUBJECT_OPEN_BY_YEAROFSEMESTER);
			pre.setLong(1, idFaculty);
			pre.setLong(2, fkYearOfSemester);
			rs = pre.executeQuery();
			while (rs.next()) {
				ListSubjectOpen listSub = new ListSubjectOpen();
				listSub.setIdSubjectOpen(rs.getLong(1));
				listSub.setNameSubject(rs.getString(2));
				listSub.setNameTeacher(rs.getString(5) + " " + rs.getString(4)
						+ " " + rs.getString(3));
				listSub.setNameRoom(rs.getString(6));
				listSub.setRankOfLesson(rs.getString(7));
				listSub.setNameOfClass(rs.getString(8));
				listSub.setSemester(rs.getInt(9));
				listSub.setYearOfSemester(rs.getString(10));
				listSub.setDateStart(rs.getString(11));
				listSub.setDateEnd(rs.getString(12));
				listSub.setWeek(rs.getInt(13));
				listSubjectOpen.add(listSub);
			}
			rs.close();
			pre.close();
			ConnectDao.close();
			return listSubjectOpen;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public boolean deleteSubjectOpenById(long id) {
		conn = ConnectDao.getConnection();
		try {
			pre = conn.prepareStatement(DELETE_SUBJECT_OPEN_BY_ID);
			pre.setLong(1, id);
			pre.executeUpdate();
			pre.close();
			ConnectDao.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	public static void main(String[] args) {
		SubjectOpenDaoImpl s = new SubjectOpenDaoImpl();
		System.out.println(s.getListSubjectOpenByYearOfSemester(1, 2));
	}

	@Override
	public List<SubjectOpen> getListSubjectOpenById(long idSubjectOpen) {
		listSbOpen = new ArrayList<SubjectOpen>();
		conn = ConnectDao.getConnection();
		try {
			pre = conn.prepareStatement(GET_SUBJECT_OPEN_BY_ID);
			pre.setLong(1, idSubjectOpen);
			rs = pre.executeQuery();
			while(rs.next()){
				SubjectOpen sb = new SubjectOpen();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
}
